package com.example.txttoxls;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Set;
import java.util.TreeSet;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import android.R.integer;
import android.graphics.Color;
import android.os.SystemProperties;
import android.util.Log;

import jxl.Cell;
import jxl.Range;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class XlsOperate {

	File file;
	String mFilePath, mFileName;
	WritableWorkbook book = null;
	int STYLE_STR = 0x001;
	int STYLE_TRUE = 0x002;
	int STYLE_FALSE = 0x003;
	int STYLE_VERSION = 0x004;

	public XlsOperate(String mFilePath, String mFileName) {
		this.mFileName = mFileName;
		this.mFilePath = mFilePath;
	}

	public void createXLS() {
		try {
			file = new File(mFilePath + mFileName + ".xls");
			Log.i("YANG", mFilePath + mFileName + ".xls");
			book = Workbook.createWorkbook(file);
			mFilePath = mFilePath;
			mFileName = mFileName + ".xls";
			// 生成名为“第一页”的工作表，参数0表示这是第一页
			WritableSheet sheet = book.createSheet("第一页", 0);
			// 在Label对象的构造子中指名单元格位置是第一列第一行(0,0)
			// 以及单元格内容为test
			Label label = new Label(0, 0, "test");
			// 将定义好的单元格添加到工作表中
			sheet.addCell(label);
			/*
			 * 生成一个保存数字的单元格 必须使用Number的完整包路径， 否则有语法歧义 单元格位置是第二列，第一行，值为789.123
			 */
			 jxl.write.Number number = new jxl.write.Number(1, 0, 789.123);
			 sheet.addCell(number);
			// 写入数据并关闭文件
			book.write();
			book.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void addData(int page, int x, int y, String data) {
		try {
			// Excel获得文件
			file = new File(mFilePath + mFileName + ".xls");
			Workbook wb = Workbook.getWorkbook(file);
			// 打开一个文件的副本，并且指定数据写回到原文件
			WritableWorkbook book = Workbook.createWorkbook(file, wb);
			WritableSheet sheet = book.getSheet(page);
			Label label = new Label(x, y, data);
			sheet.addCell(label);
			// 写入数据并关闭文件
			book.write();
			book.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void setSheets(ArrayList<String> sheetName) {
		try {
			file = new File(mFilePath + mFileName + ".xls");
			Workbook wb = Workbook.getWorkbook(file);
			WritableWorkbook book = Workbook.createWorkbook(file, wb);
			
			//写入版本号

//			WritableSheet sheet = book.getSheet("Cover");
//			String version = SystemProperties.get("ro.gn.extvernumber");
//			String project_name = version.substring(0, version.indexOf("_") - 1);
//			Label project = new Label(2, 1, project_name, setCellStyle(STYLE_VERSION));
//			sheet.addCell(project);
//			Label sw_ver = new Label(2, 3, version, setCellStyle(STYLE_VERSION));
//			sheet.addCell(sw_ver);

//			ArrayList<String> removeStr = new ArrayList<String>();
//			for (int i = 0; i < sheetName.size(); i++) {
//				for (int j = 0; j < wb.getSheetNames().length; j++) {
//					if (sheetName.get(i).equals(wb.getSheetNames()[j])) {
//						removeStr.add(sheetName.get(i));// 获得要移除的字串
//					}
//				}
//			}
//
//			// 移除指定的项
//			Iterator<String> iter = sheetName.iterator();
//			while (iter.hasNext()) {
//				String s = iter.next();
//				for (int i = 0; i < removeStr.size(); i++) {
//					if (s.equals(removeStr.get(i))) {
//						iter.remove();
//					}
//				}
//			}

//			int sheetNameCount = wb.getNumberOfSheets();
//			Log.i("YANG", sheetNameCount + "");
//			for (int i = 0; i < sheetName.size(); i++) {
//				book.createSheet(sheetName.get(i), sheetNameCount + i + 1);
//				Log.i("YANG", sheetName.get(i) + "");
//			}
			book.createSheet("ccc", 2);
			book.write();
			book.close();
			wb.close();
			Log.i("YANG", "setSheets");
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void setHeader(ArrayList<String> sheetNames) {
		file = new File(mFilePath + mFileName + ".xls");
		try {
			Workbook wb = Workbook.getWorkbook(file);
			WritableWorkbook book = Workbook.createWorkbook(file, wb);

			for (int j = 0; j < sheetNames.size(); j++) {
				WritableSheet sheet = book.getSheet(sheetNames.get(j));
				sheet.addCell(new Label(0, 0, "Index"));
				sheet.addCell(new Label(1, 0, "ID"));
				sheet.addCell(new Label(2, 0, "Module"));
				sheet.addCell(new Label(3, 0, "Suite"));
				sheet.addCell(new Label(4, 0, "Summary"));
				sheet.addCell(new Label(5, 0, "Count"));
				sheet.addCell(new Label(6, 0, "Result"));
				sheet.addCell(new Label(7, 0, "Note"));
				sheet.setColumnView(4, 30);// 设置summary列宽

				for (int i = 0; i < 8; i++) {
					WritableCell cell = sheet.getWritableCell(i, 0);
					WritableFont bold = new WritableFont(WritableFont.ARIAL,
							WritableFont.DEFAULT_POINT_SIZE, WritableFont.BOLD);
					WritableCellFormat alignStyle = new WritableCellFormat(
							bold);// 单元格样式
					alignStyle.setBackground(Colour.ICE_BLUE);
					alignStyle.setAlignment(Alignment.CENTRE);// 水平对齐
					alignStyle.setBorder(Border.ALL, BorderLineStyle.THIN,
							Colour.BLACK);//细边框
					cell.setCellFormat(alignStyle);
				}
			}
			
			book.write();
			book.close();
			wb.close();
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (RowsExceededException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public ArrayList<String> ReadTxtFile(String strFilePath) {
		String path = strFilePath;
		String content = ""; // 文件内容字符串
		ArrayList<String> fileContent = new ArrayList<String>();
		// 打开文件
		File file = new File(path);
		// 如果path是传递过来的参数，可以做一个非目录的判断
		if (file.isDirectory()) {
			Log.d("TestFile", "The File doesn't not exist.");
		} else {
			try {
				InputStream instream = new FileInputStream(file);
				if (instream != null) {
					InputStreamReader inputreader = new InputStreamReader(
							instream, "gbk");
					BufferedReader buffreader = new BufferedReader(inputreader);
					String line = null;
					// Log.i("YANG", "line-------" + line);
					// 分行读取
					while ((line = buffreader.readLine()) != null) {
						// content += line + "\n";
						fileContent.add(line);
					}
					instream.close();
				}
			} catch (java.io.FileNotFoundException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return fileContent;
	}

	public void fillCaseAndResult(ArrayList<String> caseInfos) {
		file = new File(mFilePath + mFileName + ".xls");
		try {
			Workbook wb = Workbook.getWorkbook(file);
			WritableWorkbook book = Workbook.createWorkbook(file, wb);
			//此循环用来填充结果
			for(int i = 0; i < caseInfos.size(); i++){
				String caseInfo = caseInfos.get(i);
				double id = Double.parseDouble(caseInfo.substring(getCharacterPosition(caseInfo, 1) + 1,
						getCharacterPosition(caseInfo, 2)));
				String module = caseInfo.substring(getCharacterPosition(caseInfo, 2) + 1,
						getCharacterPosition(caseInfo, 3));
				String set = caseInfo.substring(getCharacterPosition(caseInfo, 3) + 1,
						getCharacterPosition(caseInfo, 4));
				String summary = caseInfo.substring(getCharacterPosition(caseInfo, 4) + 1,
						getCharacterPosition(caseInfo, 5));
				String result;
				int style;
				if(caseInfo.substring(caseInfo.lastIndexOf("/") + 1,caseInfo.length()).equals("1")){
					result = "Pass";
					style = STYLE_TRUE;
				}else{
					result = "Fail";
					style = STYLE_FALSE;
				}
				
				WritableSheet sheet = book.getSheet(module);
				//确定行数
				int row = sheet.getColumn(1).length;
				
				//填写index
				Number label_index = new Number(0, row, row,setCellStyle(STYLE_STR));
				sheet.addCell(label_index);
				
				//填写id
				Number label_id = new Number(1, row, id,setCellStyle(STYLE_STR));
				sheet.addCell(label_id);
				
				//填写module
				Label label_module = new Label(2, row, module, setCellStyle(STYLE_STR));
				sheet.addCell(label_module);
				
				//填写sets
				Label label_set = new Label(3, row, set, setCellStyle(STYLE_STR));
				sheet.addCell(label_set);
				
				//填写summary
				Label label_summary = new Label(4, row, summary, setCellStyle(STYLE_STR));
				sheet.addCell(label_summary);
				
				//填写count
				Number label_count = new Number(5, row, 1,setCellStyle(STYLE_STR));
				sheet.addCell(label_count);
				
				//填写result
				Label label_result = new Label(6, row, result,setCellStyle(style)); 
				sheet.addCell(label_result);
				
				//填写note
				Label label_note = new Label(7, row, "", setCellStyle(STYLE_STR));
				sheet.addCell(label_note);
			}
			
			book.write();
			book.close();
			wb.close();
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	

	}
	
	public WritableCellFormat setCellStyle(int style){
		WritableCellFormat alignStyle = new WritableCellFormat();
		try {
			if(style == STYLE_STR){
				alignStyle.setAlignment(Alignment.CENTRE);// 居中
				alignStyle.setBorder(Border.ALL, BorderLineStyle.THIN,
					Colour.BLACK);//细边框
			}else if(style == STYLE_FALSE){
				alignStyle.setAlignment(Alignment.CENTRE);// 居中
				alignStyle.setBorder(Border.ALL, BorderLineStyle.THIN,
						Colour.BLACK);//细边框
				alignStyle.setBackground(Colour.RED);
			}else if(style == STYLE_TRUE){
				alignStyle.setAlignment(Alignment.CENTRE);// 居中
				alignStyle.setBorder(Border.ALL, BorderLineStyle.THIN,
						Colour.BLACK);//细边框
				alignStyle.setBackground(Colour.LIME);
			}else if(style == STYLE_VERSION){
				WritableFont bold = new WritableFont(WritableFont.ARIAL,
						15, WritableFont.BOLD);
				alignStyle = new WritableCellFormat(bold);// 单元格样式
				alignStyle.setBorder(Border.ALL, BorderLineStyle.THIN,
						Colour.BLACK);//细边框
			}
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
			return alignStyle;
	}

	
	public ArrayList<String> getCaseInfo(ArrayList<String> taskSummarys){
		ArrayList<String> caseIdSet = new ArrayList<String>();//每一页sheet的id集合
		ArrayList<String> caseInfos = new ArrayList<String>();
		file = new File(mFilePath + "ft_testCase.xls");
		String caseInfo = null;
		try {
			Workbook wb = Workbook.getWorkbook(file);
		
			for (int k = 0; k < taskSummarys.size(); k++) {
				String id = taskSummarys.get(k).substring(0, taskSummarys.get(k).indexOf("|"));
				for (int j = 0; j < wb.getSheetNames().length; j++) {// 循环对比所有sheets
					Sheet sheet = wb.getSheet(wb.getSheetNames()[j]);
	
					for (int i = 0; i < sheet.getColumn(1).length; i++) {
						caseIdSet.add(sheet.getColumn(1)[i].getContents());
					}

					if (caseIdSet.indexOf(id) != -1) {//返回的值就是case所在的行数
						caseInfo = sheet.getRow(caseIdSet.indexOf(id))[0].getContents() + "/";
						for(int i = 1; i < sheet.getRow(caseIdSet.indexOf(id)).length; i++){
							caseInfo = caseInfo + sheet.getRow(caseIdSet.indexOf(id))[i].getContents() + "/";
						}
						caseInfo = caseInfo + taskSummarys.get(k).substring(taskSummarys.get(k).lastIndexOf("|") + 1,
								taskSummarys.get(k).length());
						caseInfos.add(caseInfo);
//						Log.i("YANG", caseInfo);
						caseIdSet.clear();
						break;
					}else{
						caseIdSet.clear();
					}
				}
			}
			wb.close();
		}catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return caseInfos;
		
	}
	
	public ArrayList<String> getSheetNames(ArrayList<String> caseInfos) {
		ArrayList<String> dup_sheetNames = new ArrayList<String>();// 有重复name
		ArrayList<String> sheetNames = new ArrayList<String>();
		
		for(int i = 0; i < caseInfos.size(); i++){
			int start = getCharacterPosition(caseInfos.get(i),2) + 1;
			int end = getCharacterPosition(caseInfos.get(i),3);
			String name = caseInfos.get(i).substring(start,end);
			dup_sheetNames.add(name);
		}
		
		Set<String> set = new TreeSet<String>();
		set.addAll(dup_sheetNames);
		Iterator<String> iter = set.iterator();
		while (iter.hasNext()) {
			sheetNames.add(iter.next());
		}

		return sheetNames;
	}
	
	public int getCharacterPosition(String string,int n){
		 //这里是获取"/"符号的位置
		 Matcher slashMatcher = Pattern.compile("/").matcher(string);
		 int mIdx = 0;
		 while(slashMatcher.find()) {
			 mIdx++;
			 //当"/"符号第n次出现的位置
			 if(mIdx == n){
				 break;
			 }
		 }
		 return slashMatcher.start();
	}
	
	public void copySheet(){
		file = new File(mFilePath + mFileName + ".xls");
		try {
			Workbook wb = Workbook.getWorkbook(file);
			WritableWorkbook book = Workbook.createWorkbook(file, wb);
			
//			Sheet sheet = wb.getSheet(0);
//			book.copySheet(0, "ccc", 3);
			Log.i("YANG", wb.getNumberOfSheets() + "");
			book.copySheet(1, "ddd", wb.getNumberOfSheets());
			book.write();
			book.close();
			wb.close();
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	public int getNumberOfSheets(){
		file = new File(mFilePath + mFileName + ".xls");
		int num = 0;
		try {
			Workbook wb = Workbook.getWorkbook(file);
			num = wb.getNumberOfSheets();
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return num;
	}
}
